﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Core.Data;
using CommonModule;
using ePlanet.SingleWell.Models;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using DreamBuilder.Core.DAO;
using System.Data.OracleClient;
using System.IO;
using DreamBuilder.Common;
using System.Data.Common;
using Web.SystemConfig.SysCache;
using Web.SystemConfig.WorkFlow;

namespace ePlanet.SingleWell.DataAccess
{
    public class SingleWellDAl : DataHelper
    {
        public SingleWellDAl()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 获取代办
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public SingleWellModel.SINGLEWELLDataTable GetSingleWellDataAgent(string userID, int beginNum, int count, ref int sum)
        {
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = string.Format(@"select * from 
                                                                        (
                                                                            select s.*,rownum as n from singlewell s
                                                                            inner join workflow_operatelog l
                                                                            on s.welldataid=l.recordid
                                                                            and l.fromuserid=''
                                                                            and l.touserid is not null
                                                                            inner join
                                                                            (
                                                                                select t.welldataid,max(t.operatelogid) as realID from
                                                                                (
                                                                                select * from singlewell s
                                                                                inner join workflow_operatelog l
                                                                                on s.welldataid=l.recordid
                                                                                and l.fromuserid=''
                                                                                and l.touserid is not null
                                                                                ) t
                                                                                group by t.welldataid
                                                                            ) te 
                                                                            on te.realID=l.operatelogid
                                                                        ) tem
                                                                        where tem.n between 1 and 2", userID, beginNum, beginNum + count);

            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }

            return new SingleWellModel.SINGLEWELLDataTable();
        }

        /// <summary>
        /// 获取已办
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public SingleWellModel.SINGLEWELLDataTable GetSingleWellDataDone(string userID, int beginNum, int count, ref int sum)
        {
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = string.Format(@"select WELLDATAID,WELLCODE,INSERTTIME,MODIFYTIME,INSERTUSERID,STATUSID,DATAFILENAME,WORKMODE,A4,A3,A0,BEGINDATE,FINISHDATE,WELLTYPE,BEGINWORKDATE,FINISHWORKDATE,WELLDEPTH,ARCHIVECODE,QUANZONGCODE 
                                                            from 
                                                            (
                                                              select s.*,rownum as n from singlewell s
                                                              inner join workflow_operatelog l
                                                              on s.welldataid=l.recordid
                                                              and l.fromuserid='{0}'
                                                              and l.touserid is not null
                                                                inner join
                                                                (
                                                                  select t.welldataid,max(t.operatelogid) as realID from
                                                                  (
                                                                    select * from singlewell s
                                                                    inner join workflow_operatelog l
                                                                    on s.welldataid=l.recordid
                                                                    and l.fromuserid='{0}'
                                                                    and l.touserid is not null
                                                                  ) t
                                                                  group by t.welldataid
                                                                ) te 
                                                                on te.realID=l.operatelogid
                                                            ) tem
                                                            where tem.n between {1} and {2}", userID, beginNum, beginNum + count);

            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }

            return new SingleWellModel.SINGLEWELLDataTable();
        }

        /// <summary>
        /// 通过WellDataID获取某单井数据
        /// </summary>
        /// <param name="wellDataID"></param>
        /// <returns></returns>
        public SingleWellModel GetSingleWellDataByWellDataID(string wellDataID)
        {
            SingleWellModel rtnData = new SingleWellModel();
            string sqlSingle = string.Format(@"select * from singlewell where dataid='{0}'", wellDataID);
            string detailSql = string.Format(@"select * from welldatadetail  where welldataid='{0}'", wellDataID);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet dsSingle = this.DBHelper.ExecuteDataSet(sqlSingle);
                if (dsSingle != null && dsSingle.Tables.Count > 0)
                {
                    rtnData.SINGLEWELL.Merge(dsSingle.Tables[0]);
                }

                DataSet dsDetail = this.DBHelper.ExecuteDataSet(detailSql);
                if (dsDetail != null && dsDetail.Tables.Count > 0)
                {
                    rtnData.WELLDATADETAIL.Merge(dsDetail.Tables[0]);
                }

            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }

            return rtnData;
        }

        /// <summary>
        /// 插入新单井Model
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddNewSingleWellModel(SingleWellModel model)
        {
            bool rtnBool = false;
            if (this.DBHelper.database is OracleDatabase)
            {
                if (model != null)
                {
                    if (model.SINGLEWELL != null && model.SINGLEWELL.Rows.Count > 0)
                    {
                        DataSet singleDs = new DataSet();
                        DataTable singleDt = model.SINGLEWELL.Copy();

                        singleDs.Tables.Add(singleDt);

                        this.InsertData(singleDs);
                    }

                    if (model.WELLDATADETAIL != null && model.WELLDATADETAIL.Rows.Count > 0)
                    {
                        DataSet detailDs = new DataSet();
                        DataTable detailDt = model.WELLDATADETAIL.Copy();
                        detailDs.Tables.Add(detailDt);

                        this.InsertData(detailDs);
                    }
                }
                rtnBool = true;
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }

            return rtnBool;
        }

        /// <summary>
        /// 删除单井数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteNewSingleWellModel(SingleWellModel model)
        {
            string sql = "delete from singlewell where DATAID=:DATAID";
            DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql);
            this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, model.SINGLEWELL[0].DATAID);
            int rowcount = this.DBHelper.database.ExecuteNonQuery(cmd);
            return rowcount > 0 ? true : false;
        }

        public bool DeleteNewSingleWellModel(string dataId)
        {
            string sql = "delete from singlewell where DATAID=:DATAID";
            DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql);
            this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, dataId);
            int rowcount = this.DBHelper.database.ExecuteNonQuery(cmd);
            return rowcount > 0 ? true : false;
        }

        public SingleWellModel.SINGLEWELLDataTable GetSingleWellDatas()
        {
            SingleWellModel.SINGLEWELLDataTable rtnData = new SingleWellModel.SINGLEWELLDataTable();
            string sql = string.Format("select * from SINGLEWELL t");
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    rtnData.Merge(ds.Tables[0]);
                }
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }

            return rtnData;
        }

        public void Update(DataSet ds)
        {

        }

        internal bool SaveAttach(DataTable dt_Attach, string objectTableName, string objectID, string DBHelperString)
        {
            this.DBHelper = DBHelperFactory.CreateInstance(DBHelperString);
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
            if (dt_Attach != null && dt_Attach.Rows.Count > 0)
            {
                foreach (DataRow arow in dt_Attach.Rows)
                {
                    if (int.Parse(arow["RowStatus"].ToString()) == 3) //状态为3则是删除该列
                    {
                        try
                        {
                            string sql = string.Format("Delete FROM P_BT_Attachments Where AttachmentID='{0}'", arow["AttachmentID"].ToString());
                            DBHelper.ExecuteSql(sql);
                        }
                        catch { return false; }
                    }
                    if (int.Parse(arow["RowStatus"].ToString()) == 2)//状态为2是修改该列
                    {
                        try
                        {
                            string sql = string.Format("Update P_BT_Attachments Set ObjectTableName='{0}',ObjectID='{1}' Where AttachmentID='{2}'", objectTableName, objectID, arow["AttachmentID"].ToString());
                            DBHelper.ExecuteSql(sql);
                        }
                        catch { return false; }
                    }
                }
            }
            return true;
        }

        internal DataTable GetAttachInfo(string objectID, string DBHelperString)
        {
            this.DBHelper = DBHelperFactory.CreateInstance(DBHelperString);
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }

            string sql = string.Format("select attachmentid,attachmentname,uploadtime,attachmentsize from p_bt_attachments where objectid='{0}'", objectID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                return oDataSet.Tables[0];
            }
            else
            {
                return null;
            }
        }

        internal string GetAttachIDByDetailID(string detaileID, string DBHelperString)
        {
            this.DBHelper = DBHelperFactory.CreateInstance(DBHelperString);
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }

            string sql = string.Format("select attachmentid from p_bt_attachments a where a.objectID=(select welldataid from archive.welldatadetail d where d.detailID='{0}')", detaileID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0 && oDataSet.Tables[0].Rows.Count > 0)
            {
                return oDataSet.Tables[0].Rows[0][0].ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        internal DataTable GetAttachContentByAttactmentID(string attactmentID, string DBHelperString)
        {
            this.DBHelper = DBHelperFactory.CreateInstance(DBHelperString);
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }

            string sql = string.Format("select attachmentcontent,documenttype,attachmentname from p_bt_attachments a where a.attachmentid='{0}'", attactmentID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                return oDataSet.Tables[0];
            }
            else
            {
                return null;
            }
        }

        internal DataTable GetAllSingleWellData()
        {
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = "select * from singlewell";
                DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    return oDataSet.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
        }

        internal DataTable ExecSql(string sql)
        {
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    return oDataSet.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
        }

        public DataTable GetSingleWellAttactContentByObjectID(string detailID)
        {
            this.DBHelper = DBHelperFactory.CreateInstance("SingleWell");
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }

            string sql = string.Format("select attachmentcontent,documenttype,attachmentname from p_bt_attachments where objectID=(select welldataid from archive.welldatadetail d where d.detailid='{0}')", detailID);
            DataSet oDataSet = DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                return oDataSet.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public void uploadAttachment(string attachmentId, string objectTableName, string objectId, string attachmentName, int attachmentSize, string documentType, byte[] attachmentContent, string fileTypeId, string fileUrl, string createuserId, string remark)
        {
            try
            {
                IDBHelper helper = DBHelperFactory.CreateInstance("SingleWell");
                using (OracleConnection conn = new OracleConnection(helper.database.ConnectionString))
                {
                    conn.Open();
                    OracleTransaction tran = conn.BeginTransaction();
                    //这个事务是必须要使用的，否则无法提交成功
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.Transaction = tran;
                        ///先用一个empty_blob()，填充一个空块
                        cmd.CommandText = string.Format("INSERT INTO P_BT_ATTACHMENTS (ATTACHMENTID,OBJECTTABLENAME,OBJECTID,ATTACHMENTNAME,UPLOADTIME,ATTACHMENTSIZE,DOCUMENTTYPE,ATTACHMENTCONTENT,FILETYPEID,FILEURL,CREATEUSERID,REMARK) VALUES(" +
                        "'{0}','{1}','{2}','{3}',sysdate,{4},'{5}',empty_blob(),'{6}','{7}','{8}','{9}')", attachmentId, objectTableName, objectId, attachmentName, attachmentSize, documentType, fileTypeId, fileUrl, createuserId, remark);
                        cmd.ExecuteNonQuery();

                        ///再读取，更新
                        cmd.CommandText = string.Format("SELECT ATTACHMENTCONTENT FROM P_BT_ATTACHMENTS WHERE ATTACHMENTID='{0}' FOR UPDATE", attachmentId);
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                OracleLob lob = (OracleLob)reader.GetOracleLob(0);
                                lob.Write(attachmentContent, 0, attachmentContent.Length);
                            }
                            reader.Close();
                        }
                        tran.Commit();//提交事务
                        conn.Close();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        public void uploadAttachment(string attachmentId, string objectTableName, string objectId, string attachmentName, int attachmentSize, string documentType, Stream attachmentContent, string fileTypeId, string fileUrl, string createuserId, string remark)
        {
            try
            {
                IDBHelper helper = DBHelperFactory.CreateInstance("SingleWell");
                using (OracleConnection conn = new OracleConnection(helper.database.ConnectionString))
                {
                    conn.Open();
                    OracleTransaction tran = conn.BeginTransaction();
                    //这个事务是必须要使用的，否则无法提交成功
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.Transaction = tran;
                        ///先用一个empty_blob()，填充一个空块
                        cmd.CommandText = string.Format("INSERT INTO P_BT_ATTACHMENTS (ATTACHMENTID,OBJECTTABLENAME,OBJECTID,ATTACHMENTNAME,UPLOADTIME,ATTACHMENTSIZE,DOCUMENTTYPE,ATTACHMENTCONTENT,FILETYPEID,FILEURL,CREATEUSERID,REMARK) VALUES(" +
                        "'{0}','{1}','{2}','{3}',sysdate,{4},'{5}',empty_blob(),'{6}','{7}','{8}','{9}')", attachmentId, objectTableName, objectId, attachmentName, attachmentSize, documentType, fileTypeId, fileUrl, createuserId, remark);
                        cmd.ExecuteNonQuery();

                        ///再读取，更新
                        cmd.CommandText = string.Format("SELECT ATTACHMENTCONTENT FROM P_BT_ATTACHMENTS WHERE ATTACHMENTID='{0}' FOR UPDATE", attachmentId);
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                OracleLob lob = (OracleLob)reader.GetOracleLob(0);
                                int bufferSize = 4096;
                                byte[] buffer = new byte[bufferSize];
                                int size = 0;
                                while ((size = attachmentContent.Read(buffer, 0, bufferSize)) > 0)
                                {
                                    lob.Write(buffer, 0, size);
                                }
                            }
                            reader.Close();
                        }
                        tran.Commit();//提交事务
                        conn.Close();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        public byte[] GetAttachmentInfo(string attachmentId)
        {
            byte[] result = null;
            try
            {
                IDBHelper helper = DBHelperFactory.CreateInstance("SingleWell");
                using (OracleConnection conn = new OracleConnection(helper.database.ConnectionString))
                {
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = string.Format("SELECT ATTACHMENTCONTENT FROM P_BT_ATTACHMENTS WHERE ATTACHMENTID='{0}'", attachmentId);
                        conn.Open();
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            byte[] buffer = new byte[50];
                            MemoryStream ms = new MemoryStream();

                            if (reader.Read())
                            {
                                OracleLob blob = (OracleLob)reader.GetOracleLob(0);
                                int size = 0;
                                while ((size = blob.Read(buffer, 0, 50)) > 0)
                                {
                                    ms.Write(buffer, 0, size);
                                }
                                blob.Close();
                            }
                            reader.Close();
                            ms.Position = 0;
                            result = new byte[ms.Length];
                            ms.Read(result, 0, result.Length);
                        }
                        conn.Close();
                        return result;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 根据编目ID获取单井资料
        /// </summary>
        /// <param name="pageinfo"></param>
        /// <param name="wellCatalogId"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public DataTable GetPageSingleWellByCatalogId(PageInfo pageinfo, string wellCatalogId, string orderBy)
        {
            DataTable singleWellTable = null;
            string selectSql, countSql;

            int finishStatusID = new ArchiveCacheFacade().GetStatusRuleCache().GetFinishStatus((int)ArchiveTableEnum.SingleWell);
            if (wellCatalogId == "0" || wellCatalogId.Trim() == "")
            {
                selectSql = string.Format(@"select * from (select rownum rn, b.* from singlewell b    {0})a where statusID={3} and rn > {1} and rn <= {2}",
                orderBy, pageinfo.PageSize * (pageinfo.PageIndex - 1), pageinfo.PageSize * pageinfo.PageIndex, finishStatusID);
                countSql = string.Format(@"select count(*) from singlewell b ");
            }
            else
            {
                selectSql = string.Format(@"select * from (select rownum rn, b.* from singlewell b where b.statusID={3} and b.wellcatalogid='{0}'   {1})a where rn >= {2} and rn <= {3}",
                wellCatalogId, orderBy, pageinfo.PageSize * (pageinfo.PageIndex - 1), pageinfo.PageSize * pageinfo.PageIndex);
                countSql = string.Format(@"select count(*) from singlewell b where b.wellcatalogid='{0}'", wellCatalogId);
            }

            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet oDataSet = DBHelper.ExecuteDataSet(selectSql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    singleWellTable = oDataSet.Tables[0];
                }
                pageinfo.RecordCount = int.Parse(DBHelper.ExecuteScalar(countSql).ToString());
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return singleWellTable;
        }

        public int GetSingleWellCount(string wellCatalogId)
        {
            try
            {
                if (this.DBHelper.database is OracleDatabase)
                {
                    string sql = "select count(*) from singlewell where wellcatalogid ='" + wellCatalogId + "'";
                    return int.Parse(DBHelper.ExecuteScalar(sql).ToString());
                }
                else
                {
                    throw new Exception("不支持Oracle以外的数据库");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteDetail(string detailID)
        {
            try
            {
                if (this.DBHelper.database is OracleDatabase)
                {
                    string sql = "delete from welldatadetail where detailid='" + detailID + "'";
                    DBHelper.ExecuteSql(sql);
                }
                else
                {
                    throw new Exception("不支持Oracle以外的数据库");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable StatisticsSinglewellWorkmode(int pageSize, int pageIndex, out int recordCount)
        {
            string countSql = "SELECT DISTINCT workmode FROM singlewell ";
            DataTable table = null;
            string statistcsSql = string.Format(@"SELECT * FROM 
                                    (
                                    SELECT B.*, ROWNUM RN
                                      FROM (SELECT A.WORKMODE,
                                                   SUM(A.A4 + A.A3 + A.A0) PCOUNT,
                                                   COUNT(A.DATAID) RCOUNT
                                              FROM SINGLEWELL A
                                             GROUP BY A.WORKMODE ORDER BY PCOUNT DESC)  B
                                    ) M
                                    WHERE m.rn BETWEEN {0} AND {1}",
                                    pageSize * (pageIndex-1) + 1,
                                    pageSize * pageIndex);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet countDataset = DBHelper.ExecuteDataSet(countSql);
                if (countDataset != null && countDataset.Tables.Count > 0)
                {
                    recordCount = countDataset.Tables[0].Rows.Count;
                }
                else
                {
                    recordCount = 0;
                }
                DataSet oDataSet = DBHelper.ExecuteDataSet(statistcsSql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    table = oDataSet.Tables[0];
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return table;
        }

        public DataTable StatisticsSinglewellWorkmode()
        {
            DataTable table = null;
            string statistcsSql = @"SELECT B.*, ROWNUM RN
                                      FROM (SELECT A.WORKMODE,
                                                   SUM(A.A4 + A.A3 + A.A0) PCOUNT,
                                                   COUNT(A.DATAID) RCOUNT
                                              FROM SINGLEWELL A
                                             GROUP BY A.WORKMODE)  B";
            if (this.DBHelper.database is OracleDatabase)
            {
                
                DataSet oDataSet = DBHelper.ExecuteDataSet(statistcsSql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    table = oDataSet.Tables[0];
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return table;
        }

        public DataTable StatisticsSinglewell(string startDate, string endDate)
        {
            DataTable table = null;
            string statistcsSql = @"select count(distinct(wellcode)) allwells,
       count(*) allitems,
       sum(nvl(a4, 0) + nvl(a3, 0) + nvl(a0, 0)) allpages
  from singlewell t
 inner join workflow_operatelog tt
    on tt.recordid = t.dataid
 where t.statusid = 3
 and tt.signtime between date'{0}' and date'{1}'";
            if (this.DBHelper.database is OracleDatabase)
            {

                DataSet oDataSet = DBHelper.ExecuteDataSet(
                    string.Format(statistcsSql, startDate, endDate));
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    table = oDataSet.Tables[0];
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return table;
        }
    }
}
